<?php

namespace App\Services;

use App\AwardAndExpertGroupRelations;
use App\AwardApplicationFormData;
use App\AwardGroupAndExpertGroupRelations;
use App\Consts;
use App\ConvertTask;
use App\ExpertAndExpertGroupRelations;
use App\ExpertGroup;
use App\Jobs\ConvertAwardFormDataToPdfJob;
use App\WorkflowPhase;
use DB;
use App\Project;
use App\User;
use stdClass;
use App\AwardGroup;
use App\AwardAndAwardGroupRelations;

class AwardService
{
    public function __construct()
    {
        $this->awardsConfig = config('vars.awards');
        $this->logSvc = new LogService;
    }

    /**
     * 获取申报进行中（尚未截止）的奖项 id
     *
     * @return array 奖项 id 数组
     */
    public function getApplicationOpenAwardIds()
    {
        $openAwardIds = [];
        $Work = WorkflowPhase::find(1);
        if ($Work->has_begun != 1 || $Work->has_ended != 0) {
            // 进步奖申报阶段结束
        } else {
            $openAwardIds[] = 1; // 进步奖
        }
        $openAwardIds[] = 2; // 科技人物奖
        return $openAwardIds;
    }

    public function getNominators(User $user, $forClient = true)
    {
        if ($user->is_tester) {
            $nominators = User::where('is_nominator', 1)
                ->orderBy('nominator_group_id')
                ->select('id', 'nominator_group_id as group_id', 'name')
                ->get();
        } else {
            $nominators = User::where('is_nominator', 1)
                ->where('is_tester', 0)
                ->orderBy('nominator_group_id')
                ->select('id', 'nominator_group_id as group_id', 'name')
                ->get();
        }
        if ($forClient) {
            $nominatorGroups = config('vars.nominator_groups');
            foreach ($nominators as $nominator) {
                if (is_null($nominator->group_id)) {
                    continue;
                }
                $nominator->group_name = $nominatorGroups[$nominator->group_id]['name'];
                $nominator->type = $nominatorGroups[$nominator->group_id]['type'];
            }
        }
        return $nominators;
    }

    public function getNominatorsByIds($nominatorIdsArr)
    {
        $nominators = User::whereIn('id', $nominatorIdsArr)->pluck('name');
        return $nominators;
    }

    public function applicantSubmitApplicationForm(User $user, Project $application)
    {
        // TODO 权限检查
        if ($user->isOrgUser() && !$user->isOrgCertified()) {
            $certifyStatus = $user->getOrgCertifyStatus();
            if ($certifyStatus == 'certifying') {
                return '提交失败：单位用户认证未通过，请等待管理员审核通过后再提交';
            } elseif ($certifyStatus == 'failed') {
                return '提交失败：单位用户认证失败，请联系管理员';
            }
        }
        $applicationFormError = $this->validateApplicationForm($application);
        if (!empty($applicationFormError)) {
            return '提交失败：' . $applicationFormError;
        }
        $this->updateAwardProjectName($application);
        if ($application->status_id == Project::STATUS_FORMAL_REVIEW_FILL_APPLICATION) {
            if ($this->isNominationRequired($application->award_id)) {
                $this->createNominationRequests($application);
                $this->updateApplicationStatus($application, $user, 'A2');
            } else {
                $this->updateApplicationStatus($application, $user, Project::STATUS_FORMAL_REVIEW_TO_REVIEW);
            }
        } elseif ($application->status_id == Project::STATUS_FORMAL_REVIEW_RETURNED_TO_FILL) {
//            $this->resetUnsuccessfulNominationRequests($application);
            $this->updateApplicationStatus($application, $user, Project::STATUS_FORMAL_REVIEW_TO_REVIEW);
        }
        $application->submitted_at = now();
        $application->attachment_processed = 0;
        $application->save();
        return null;
    }

    public function updateAwardProjectName(Project $application)
    {
        $applicationFormData = AwardApplicationFormData::find($application->id);
        $formData = json_decode($applicationFormData->form_data, true);
        if ($formData['type'] == 'progress') {
            $projectName = $formData['basic_situation']['project_name'];
        } else if ($formData['type'] == 'talent') {
            $projectName = $formData['basic']['name'];
        }
        $application->project_name = $projectName;
        $application->save();

    }

    public function nominatorAgreeToNominate(User $user, Project $application)
    {
        // TODO 权限检查
        DB::update(
            'update nomination_requests set result = ? where nominator_id = ? and application_id = ?',
            ['同意', $user->id, $application->id]
        );
        $this->logSvc->info('同意' . config('vars.strings.nomination'), null, $user, $application);
        // TODO 待修正: 所有单位均同意推荐才更新状态
        $this->updateApplicationStatus($application, $user, Project::STATUS_FORMAL_REVIEW_TO_REVIEW);
        return null;
    }

    public function nominatorReturnApplication(User $user, Project $application)
    {
        // TODO 权限检查
        DB::update(
            'update nomination_requests set result = ? where nominator_id = ? and application_id = ?',
            ['退回', $user->id, $application->id]
        );
        $this->updateApplicationStatus($application, $user, 'A1.1');
        return null;
    }

    private function isNominationRequired($awardId)
    {
        $awardInfo = $this->getAwardInfo($awardId);
        return $awardInfo['nomination_required'];
    }

    private function createNominationRequests(Project $application)
    {
        $nominatorIds = explode(',', $application->nominator_ids);
        foreach ($nominatorIds as $nominatorId) {
            DB::statement(
                'insert ignore into nomination_requests set nominator_id = ?, application_id = ?',
                [$nominatorId, $application->id]
            );
        }
    }

    public function updateApplicationDocPath(Project $application, $path)
    {
        $application->doc_path = $path;
        $application->save();
    }

    private function findStatusTransitionDef($srcStatusId, $destStatusId)
    {
        $statusTransitionDefs = config('vars.status_transition_defs');
        foreach ($statusTransitionDefs as $def) {
            if ($def['src'] === $srcStatusId && $def['dest'] === $destStatusId) {
                return $def;
            }
        }
        return null;
    }

    public function getStatusDef($statusId)
    {
        return config('vars.status_defs')[$statusId];
    }

    /**
     * 校验申报书内容是否完整, 返回错误信息
     *
     * @return null 如果检验通过没有发现问题
     * @return error 否则返回 string 类型的错误提示
     */
    public function validateApplicationForm(Project $application)
    {
        $formData = json_decode(AwardApplicationFormData::find($application->id)->form_data);
        require_once base_path('vars/code/functions.php');
        $awardInfo = $this->getAwardInfo($application->award_id);
        return validateApplicationFormData($awardInfo, $formData);
    }

    public function createApplication(User $user, $awardId, $nominatorIds)
    {
        DB::beginTransaction();
        $awardInfo = $this->getAwardInfo($awardId);
        $application = new Project;
        $application->status_id = Project::STATUS_FORMAL_REVIEW_FILL_APPLICATION;
        $application->applicant_editable = 1;
        $application->user_id = $user->id;
        $application->award_id = $awardInfo['id'];
        $application->nominator_ids = implode(',', $nominatorIds);
        $application->save();
        $applicationFormData = new AwardApplicationFormData;
        $applicationFormData->id = $application->id;
        $applicationFormData->save();
        DB::commit();
        return $application;
    }

    public function saveApplicationFormData(Project $application, $formData)
    {
        $applicationFormData = AwardApplicationFormData::find($application->id);
        $applicationFormData->form_data = json_encode($formData, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);
        $applicationFormData->save();
    }

    public function checkUserQuota(User $user, $awardId)
    {
        $awardInfo = $this->getAwardInfo($awardId);
        $count = $this->countUserApplicationsForAward($user, $awardId);
        $quota = $awardInfo['quota_per_applicant'];
        return $count < $quota;
    }

    public function countUserApplicationsForAward(User $user, $awardId)
    {
        $count = Project::where('user_id', $user->id)
            ->where('award_id', $awardId)
            ->count();
        return $count;
    }

    public function getUserApplications(User $user, $role, $forClient = true)
    {
        if ($role === 'applicant') {
            $applications = Project::where('user_id', $user->id)
                ->get();
        } elseif ($role === 'won_awards') {
            $applications = Project::where('user_id', $user->id)
                ->where('status_id', 'E4')
                ->get();
        } elseif ($role === 'nominator') {
            $rows = DB::table('nomination_requests')
                ->where('nominator_id', $user->id)
                ->pluck('application_id');
            $applicationIds = $rows->toArray();
            $applications = Project::whereIn('id', $applicationIds)
                ->orderBy('created_at', 'desc')
                ->get();
        } else {
            // TODO
        }
        if ($forClient) {
            $applicantIds = $applications->map(function ($record) {
                return $record->user_id;
            })->unique();
            $applicants = User::whereIn('id', $applicantIds)
                ->select('id', 'name', 'work_org_name', 'tel', 'email')
                ->get();
            $applicantMap = [];
            foreach ($applicants as $applicant) {
                $applicantMap[$applicant->id] = $applicant;
            }
            foreach ($applications as $application) {
                if (empty($application->nominator_ids)) {
                    $application->nominator_ids = [];
                } else {
                    $application->nominator_ids = explode(',', $application->nominator_ids);
                }
                $applicant = $applicantMap[$application->user_id];
                $application->award_status = empty($application->award) ? 0 : 1;
                $application->applicant_name = $applicant->name;
                $application->applicant_org_name = $applicant->work_org_name;
                $application->applicant_tel = $applicant->tel;
                $application->applicant_email = $applicant->email;
                $application->status_info = $this->getStatusDef($application->status_id);
            }
        }
        return $applications;
    }

    public function getAwardName($awardId)
    {
        return $this->getAwardInfo($awardId)['name'];
    }

    public function getAwardFormView($awardId, $canEdit = true)
    {
        if ($canEdit) {
            return $this->getAwardInfo($awardId)['form_view'];
        } else {
            return $this->getAwardInfo($awardId)['form_view_readonly'];
        }
    }

    public function getAwardPrintView($awardId)
    {
        return $this->getAwardInfo($awardId)['print_view'];
    }

    public function getAwardInfo($awardId)
    {
        if (array_key_exists($awardId, $this->awardsConfig)) {
            return $this->awardsConfig[$awardId];
        }
        return null;
    }

    public function getApplicationsByStatusId($statusIdArr, $searchFields = null)
    {
        $applications = DB::table('applications as A')->whereIn('status_id', $statusIdArr)
            ->leftJoin('award_and_expert_group_relations as B', 'A.id', '=', 'B.award_id')
            ->leftJoin('application_groups as C', 'B.award_group_id', '=', 'C.id')
            ->orderBy('A.updated_at', 'desc')
            ->select('A.id', 'A.award_id', 'A.project_name', 'A.user_id', 'A.status_id', 'B.expert_group_id', 'C.group_name', 'A.pdf_file_save_path')
            ->get();
        $applicantIds = $applications->map(function ($record) {
            return $record->user_id;
        })->unique();
        $applicants = User::whereIn('id', $applicantIds)
            ->select('id', 'name', 'work_org_name', 'tel', 'email')
            ->get();
        $applicantMap = [];
        foreach ($applicants as $applicant) {
            $applicantMap[$applicant->id] = $applicant;
        }
        foreach ($applications as $application) {
            if (empty($application->nominator_ids)) {
                $application->nominator_ids = [];
            } else {
                $application->nominator_ids = explode(',', $application->nominator_ids);
            }
            $applicant = $applicantMap[$application->user_id];
            $application->applicant_name = $applicant->name;
            $application->applicant_org_name = $applicant->work_org_name;
            $application->applicant_tel = $applicant->tel;
            $application->applicant_email = $applicant->email;
            $application->status_info = $this->getStatusDef($application->status_id);
        }
//        if (!empty($searchFields)) {
//            $q = $searchFields['keyword'];
//            $awardTypeId = $searchFields['awardType'];
//            $awardStatus = $searchFields['awardStatus'];
//            $filterApplications = [];
//            foreach ($applications as $application) {
//                $check_1 = true;
//                if ($awardTypeId != -1) {
//                    $check_1 = ($application->award_id == $awardTypeId) && $check_1;
//                }
//                $check_2 = true;
//                if (!empty($q)) {
//                    $check_2 = (str_contains($application->applicant_org_name, $q) || str_contains($application->applicant_name, $q)) && $check_2;
//                }
//                $check_3 = true;
//                if ($awardStatus != -1) {
//                    $check_3 = $application->status_info['status'] == $awardStatus && $check_3;
//                }
//                if ($check_1 && $check_2 && $check_3) {
//                    array_push($filterApplications, $application);
//                }
//            }
//            return $filterApplications;
//        } else {
//            return $applications;
//        }
        return $applications;
    }

    public function createAwardConvertTask($application)
    {
        $applicationFormData = AwardApplicationFormData::find($application->id);
        if ($application->award_id == 1) {
            $awardType = 'progress';
        } else if ($application->award_id == 2) {
            $awardType = 'talent';
        }
        $convertJob = new ConvertTask;
        $convertJob->award_id = $application->id;
        $convertJob->award_type = $awardType;
        $convertJob->json_data = $applicationFormData->form_data;
        $convertJob->save();
        return $convertJob;
    }

    public function deleteApplicationAndFormData(Project $application)
    {
        DB::beginTransaction();
        AwardApplicationFormData::where('id', $application->id)->delete();
        $application->delete();
        DB::commit();
    }

    //new
    public function updateApplicationStatus(Project $application, User $user, $targetStatusId, $reason = '')
    {
        $application->status_remarks = $reason;
        $application->status_id = $targetStatusId;
        $application->save();
        if ($targetStatusId == config('vars.create_convert_job_status_id')) {
            $task = $this->createAwardConvertTask($application);
            ConvertAwardFormDataToPdfJob::dispatch($task);
        }
        $statusInfo = $this->getStatusDef($targetStatusId);
        $actionType = "评审：{$statusInfo['phase']}-{$statusInfo['status']}";
        $this->logSvc->info($actionType, "", $user, $application);
    }

    public function getAdminAsideStatisticsInfo()
    {
        $statisticsInfo = new StdClass();
        $statisticsInfo->orgCertiyTotalCount = User::where('entity_type', 'org')->where('org_certify_status', 'certifying')->count();
        $statisticsInfo->applicantTotalCount = User::where('is_applicant', 1)->count();
        $statisticsInfo->formalReviewWaitingForReviewTotalCount = Project::where('status_id', Project::STATUS_FORMAL_REVIEW_TO_REVIEW)->count();
        $statisticsInfo->formalReviewWaitingForReviewAward1Count = Project::where('status_id', Project::STATUS_FORMAL_REVIEW_TO_REVIEW)->where('award_id', 1)->count();
        $statisticsInfo->formalReviewWaitingForReviewAward2Count = Project::where('status_id', Project::STATUS_FORMAL_REVIEW_TO_REVIEW)->where('award_id', 2)->count();
        $statisticsInfo->formalReviewPassedTotalCount = Project::whereIn('status_id',
            [Project::STATUS_ASSIGN_REVIEW_TO_ASSIGN, Project::STATUS_ASSIGN_REVIEW_TO_SUBMIT_ASSIGN])->count();
        $statisticsInfo->waitingForExpertReviewTotalCount = Project::whereIn('status_id', ['A6', 'A5'])->count();
        $statisticsInfo->awardGroupTotalCount = AwardGroup::whereNotNull('id')->count();
        $statisticsInfo->expertGroupTotalCount = ExpertGroup::whereNotNull('id')->count();
        return $statisticsInfo;
    }

    public function getAwardGroupsPageData($params)
    {
        $filters = json_decode($params['filters'], true);
        $q = $filters['keyword'];
        if (empty($q)) {
            $query = DB::table('application_groups as A')->whereNotNull('A.id');
        } else {
            $query = DB::table('application_groups as A')->where('A.group_name', 'like', "%$q%");
        }
        $awardTypeId = $filters['awardTypeId'];
        if ($awardTypeId) {
            $query->where('A.award_type_id', $awardTypeId);
        }
        $query->leftJoin('application_and_application_group_relations as B', 'A.id', '=', 'B.application_group_id')
            ->groupBy('A.id', 'A.group_name', 'A.award_type_id', 'A.created_at', 'A.locked')
            ->select(DB::raw('count(B.application_id) as totalCount, A.id, A.group_name, A.award_type_id, A.created_at, A.locked'));
        $pageSize = $params['pageSize'];
        $pageData = $query->paginate($pageSize);
        foreach ($pageData as $k => $data) {
            $expertGroupTotalCount = AwardGroupAndExpertGroupRelations::where('award_group_id', $data->id)->count();
            $pageData[$k]->expertGroupTotalCount = $expertGroupTotalCount;
        }
        return $pageData;
    }

    public function getAwardGroupByGroupNameAndAwardTypeId($groupName, $awardTypeId)
    {
        return AwardGroup::where('group_name', $groupName)->where('award_type_id', $awardTypeId)->first();
    }

    public function createAwardGroup($groupName, $awardTypeid)
    {
        $awardGroup = new AwardGroup;
        $awardGroup->group_name = $groupName;
        $awardGroup->award_type_id = $awardTypeid;
        $awardGroup->save();
    }

    public function deleteAwardGroup(AwardGroup $group)
    {
        DB::beginTransaction();
        AwardGroupAndExpertGroupRelations::where('award_group_id', $group->id)->delete();
        //删除项目组分配到评审小组的关联
        AwardAndExpertGroupRelations::where('award_group_id', $group->id)->delete();
        AwardAndAwardGroupRelations::where('application_group_id', $group->id)->delete();
        $group->delete();
        DB::commit();
    }

    public function canDeleteAwardGroup(AwardGroup $group)
    {
        $relations = AwardAndAwardGroupRelations::where('application_group_id', $group->id)->get()->toArray();
        if (empty($relations)) {
            return true;
        } else {
            return false;
        }
    }

    public function getCanBeAllocatedAwards($filters, $groupId, $awardTypeId)
    {
        $q = $filters['keyword'];
        if (empty($q)) {
            $query = DB::table('applications as A');
        } else {
            $query = DB::table('applications as A')
                ->where('A.project_name', 'like', "%$q%");
        }
        $query->where('A.award_id', $awardTypeId);
        $isInGroupFilter = $filters['is_in_group'];
        if ($isInGroupFilter) {
            if ($isInGroupFilter == 'has') {
                $applications = $query->where('A.status_id', 'A5')
                    ->join('application_and_application_group_relations as B', 'A.id', '=', 'B.application_id')
                    ->where('B.application_group_id', '<>', $groupId)
                    ->select('A.project_name', 'A.id', 'A.user_id', 'A.award_id', 'B.application_group_id')
                    ->get();
            } else {
                $applications = $query->where('A.status_id', 'A5')
                    ->leftJoin('application_and_application_group_relations as B', 'A.id', '=', 'B.application_id')
                    ->whereNull('B.application_group_id')
                    ->select('A.project_name', 'A.id', 'A.user_id', 'A.award_id', 'B.application_group_id')
                    ->get();
            }

        } else {
            $applications = $query->where('A.status_id', 'A5')
                ->leftJoin('application_and_application_group_relations as B', 'A.id', '=', 'B.application_id')
//                ->where('B.application_group_id', '<>', $groupId)
                ->select('A.project_name', 'A.id', 'A.user_id', 'A.award_id', 'B.application_group_id')
                ->get();
        }
        $applicantIds = $applications->map(function ($record) {
            return $record->user_id;
        })->unique();
        $applicants = User::whereIn('id', $applicantIds)
            ->select('id', 'name', 'work_org_name', 'tel', 'email')
            ->get();
        $applicantMap = [];
        foreach ($applicants as $applicant) {
            $applicantMap[$applicant->id] = $applicant;
        }
        $applications = $applications->toArray();
        foreach ($applications as $k => $application) {
            $applicant = $applicantMap[$application->user_id];
            $application->applicant_name = $applicant->name;
            $application->applicant_org_name = $applicant->work_org_name;
            $application->applicant_tel = $applicant->tel;
            $application->applicant_email = $applicant->email;
            if ($application->application_group_id == $groupId) {
                //去掉了分到自己组的
                unset($applications[$k]);
            }
        }
        $applications = array_values($applications);
        return $applications;
    }

    public function getAllocatedAwards($filters, $groupId, $awardTypeId)
    {
        $q = $filters['keyword'];
        if (empty($q)) {
            $query = DB::table('applications as A');
        } else {
            $query = DB::table('applications as A')
                ->where('A.project_name', 'like', "%$q%");
        }
        $query->where('A.award_id', $awardTypeId);
        $applications = $query->whereIn('A.status_id', ['A5', 'A6'])
            ->join('application_and_application_group_relations as B', 'A.id', '=', 'B.application_id')
            ->where('B.application_group_id', $groupId)
            ->select('A.project_name', 'A.id', 'A.user_id', 'A.award_id', 'B.application_group_id')
            ->get();
        $applicantIds = $applications->map(function ($record) {
            return $record->user_id;
        })->unique();
        $applicants = User::whereIn('id', $applicantIds)
            ->select('id', 'name', 'work_org_name', 'tel', 'email')
            ->get();
        $applicantMap = [];
        foreach ($applicants as $applicant) {
            $applicantMap[$applicant->id] = $applicant;
        }
        $applications = $applications->toArray();
        foreach ($applications as $k => $application) {
            $applicant = $applicantMap[$application->user_id];
            $application->applicant_name = $applicant->name;
            $application->applicant_org_name = $applicant->work_org_name;
            $application->applicant_tel = $applicant->tel;
            $application->applicant_email = $applicant->email;
        }
        return $applications;
    }

    public function getApplicationsByGroupId($groupId)
    {
        $applicationIds = AwardAndAwardGroupRelations::where('application_group_id', $groupId)->pluck('application_id');
        return $applicationIds;
    }

    public function importAwardsToAwardGroup($groupId, $new_relations)
    {
        if (!empty($new_relations)) {
            DB::beginTransaction();
            //如果提交的项目已经分组，则删除关联
            $changeApplicationIds = array_column($new_relations, 'application_id');
            AwardAndAwardGroupRelations::whereIn('application_id', $changeApplicationIds)->delete();
            //插入该分组新的关联
            AwardAndAwardGroupRelations::insert($new_relations);
            DB::commit();
        }
    }

    public function deleteAwardsInAwardGroupByIds($groupId, $deleteAwardIds)
    {
        AwardAndAwardGroupRelations::where('application_group_id', $groupId)->whereIn('application_id', $deleteAwardIds)->delete();
    }

    public function updateAwardBasicInfoById(AwardGroup $group, $targetInfo)
    {
        $group->group_name = $targetInfo['group_name'];
        $group->save();
    }

    public function getAwardGroupCanBeAllocatedExpertGroups($filters, $awardGroupId)
    {
        $q = $filters['keyword'];
        if (empty($q)) {
            $query = DB::table('expert_groups as A');
        } else {
            $query = DB::table('expert_groups as A')
                ->where('A.group_name', 'like', "%$q%");
        }
        $query->whereNotNull('leader_expert_id')->leftJoin('users as C', 'A.leader_expert_id', '=', 'C.id');
        $isInGroupFilter = $filters['is_in_group'];
        if ($isInGroupFilter) {
            if ($isInGroupFilter == 'has') {
                $query->join('award_group_and_expert_group_relations as B', 'A.id', '=', 'B.expert_group_id')
                    ->where('B.award_group_id', '<>', $awardGroupId);
            } else {
                $query->leftJoin('award_group_and_expert_group_relations as B', 'A.id', '=', 'B.expert_group_id')
                    ->whereNull('B.award_group_id');
            }
        } else {
            $query->leftJoin('award_group_and_expert_group_relations as B', 'A.id', '=', 'B.expert_group_id');
        }
        $expertGroups = $query->select('A.group_name', 'A.id', 'C.name as leader_name', 'B.award_group_id')->get()->toArray();
        foreach ($expertGroups as $k => $group) {
            if ($group->award_group_id === $awardGroupId) {
                //去掉了分到自己组的
                unset($expertGroups[$k]);
            }
            $checkHasSameExpertGroup = AwardGroupAndExpertGroupRelations::where('award_group_id', $awardGroupId)->where('expert_group_id', $group->id)->first();
            if (!empty($checkHasSameExpertGroup)) {
                unset($expertGroups[$k]);
            }
        }
        $expertGroups = array_values($expertGroups);
        return $expertGroups;
    }

    public function importExpertGroupsToAwardGroup($new_relations)
    {
        if (!empty($new_relations)) {
            //如果提交的项目已经分组，则删除关联
//            $changeApplicationIds = array_column($new_relations, 'application_id');
//            AwardAndAwardGroupRelations::whereIn('application_id', $changeApplicationIds)->delete();
            //插入该分组新的关联
            AwardGroupAndExpertGroupRelations::insert($new_relations);
        }
    }

    public function getAwardGroupAllocatedExpertGroups($filters, $groupId)
    {
        $q = $filters['keyword'];
        if (empty($q)) {
            $query = DB::table('expert_groups as A');
        } else {
            $query = DB::table('expert_groups as A')
                ->where('A.group_name', 'like', "%$q%");
        }
        $query->whereNotNull('leader_expert_id')->leftJoin('users as C', 'A.leader_expert_id', '=', 'C.id');
        $expertGroups = $query->join('award_group_and_expert_group_relations as B', 'A.id', '=', 'B.expert_group_id')
            ->where('B.award_group_id', $groupId)
            ->select('A.group_name', 'A.id', 'C.name as leader_name')
            ->get();
        return $expertGroups;
    }

    public function deleteExpertGroupsInAwardGroupByIds($groupId, $deleteAwardIds)
    {
        AwardGroupAndExpertGroupRelations::where('award_group_id', $groupId)->whereIn('expert_group_id', $deleteAwardIds)->delete();
    }

    public function allocateAwardToExpertGroup(AwardGroup $awardGroup)
    {
        $awardIdsArr = AwardAndAwardGroupRelations::where('application_group_id', $awardGroup->id)->pluck('application_id')->toArray();
        if (count($awardIdsArr) == 0) {
            return '该项目组内未添加项目, 请添加项目后再分配';
        }
        $expertGroupsIdArr = AwardGroupAndExpertGroupRelations::where('award_group_id', $awardGroup->id)->pluck('expert_group_id')->toArray();
        if (count($expertGroupsIdArr) == 0) {
            return '该项目组内未添加评审小组, 请添加评审小组后再分配';
        }
        if (count($awardIdsArr) < count($expertGroupsIdArr)) {
            return '该项目组中的项目数量小于评审小组数量，请调整后再分配';
        }
        $ref = count($awardIdsArr) % count($expertGroupsIdArr);
        if ($ref == 0) {
            $perGroupsCount = intval(count($awardIdsArr) / count($expertGroupsIdArr));
        } else {
            $perGroupsCount = intval(count($awardIdsArr) / count($expertGroupsIdArr)) + 1;
        }
        shuffle($awardIdsArr);
        $randomGroups = array_chunk($awardIdsArr, $perGroupsCount);
        $newRelationsArr = [];
        foreach ($randomGroups as $itemKey => $item) {
            foreach ($item as $k => $v) {
                $newRelationsArr[] = ['award_id' => $v, 'expert_group_id' => $expertGroupsIdArr[$itemKey], 'award_group_id' => $awardGroup->id];
            }
        };
        DB::beginTransaction();
        AwardAndExpertGroupRelations::where('award_group_id', $awardGroup->id)->delete();
        AwardAndExpertGroupRelations::insert($newRelationsArr);
        ExpertGroup::whereIn('id', $expertGroupsIdArr)->update(['locked' => 1]);
        Project::whereIn('id', $awardIdsArr)->update(['status_id' => 'A6']);
        $awardGroup->locked = 1;
        $awardGroup->arranged_review = 1;
        $awardGroup->save();
        DB::commit();
        return null;
    }

    public function getAwardGroupReviewArrangementInfo(AwardGroup $group)
    {
        $expertGroupsInfo = DB::table('award_group_and_expert_group_relations as A')->where('A.award_group_id', $group->id)
            ->join('expert_groups as B', 'A.expert_group_id', '=', 'B.id')
            ->leftJoin('users as C', 'B.leader_expert_id', '=', 'C.id')
            ->select('B.id as expert_group_id', 'B.group_name as expert_group_name', 'C.name as expert_group_leader_name')
            ->get()->toArray();
        foreach ($expertGroupsInfo as $k => $expertGroup) {
            $expertsInfo = DB::table('expert_and_expert_group_relations as A')->where('expert_group_id', $expertGroup->expert_group_id)
                ->join('users as B', 'A.expert_id', '=', 'B.id')
                ->select('A.expert_id', 'B.name', 'B.work_org_name')
                ->get()->toArray();
            $expertGroupsInfo[$k]->expertsInfo = $expertsInfo;
            $awardsInfo = DB::table('award_and_expert_group_relations as A')->where('A.expert_group_id', $expertGroup->expert_group_id)
                ->join('applications as B', 'A.award_id', '=', 'B.id')
                ->join('users as C', 'B.user_id', '=', 'C.id')
                ->select('A.award_id', 'B.project_name', 'C.name as applicant_name')
                ->get()->toArray();
            $expertGroupsInfo[$k]->awardsInfo = $awardsInfo;
        }
        return $expertGroupsInfo;
    }

    public function getWaitingForReviewAwardInfoByExpert(User $user)
    {
        $expertGroupIdsArr = ExpertAndExpertGroupRelations::where('expert_id', $user->id)->pluck('expert_group_id');
        $awardsIdsArr = AwardAndExpertGroupRelations::whereIn('expert_group_id', $expertGroupIdsArr)->pluck('award_id');
        $applications = DB::table('applications as A')->whereIn('A.id', $awardsIdsArr)
//            ->leftJoin('expert_scoring_application_relations as B', 'A.id', '=', 'B.application_id')
            ->leftJoin('expert_scoring_application_relations as B', function ($join) use ($user) {
                $join->on('A.id', '=', 'B.application_id')->where('B.expert_id', $user->id);
            })
            ->orderBy('A.updated_at', 'desc')
            ->select('A.id', 'B.id as relation_id', 'A.user_id', 'A.project_name', 'A.award_id', 'A.status_id', 'B.expert_id', 'B.score', 'B.is_submitted', 'B.form_data', 'B.application_id')
            ->get();
        $applicantIds = $applications->map(function ($record) {
            return $record->user_id;
        })->unique();
        $applicants = User::whereIn('id', $applicantIds)
            ->select('id', 'name', 'work_org_name', 'tel', 'email')
            ->get();
        $applicantMap = [];
        foreach ($applicants as $applicant) {
            $applicantMap[$applicant->id] = $applicant;
        }
        foreach ($applications as $application) {
            $applicant = $applicantMap[$application->user_id];
            $application->applicant_name = $applicant->name;
            $application->applicant_org_name = $applicant->work_org_name;
            $application->applicant_tel = $applicant->tel;
            $application->applicant_email = $applicant->email;
            $application->status_info = $this->getStatusDef($application->status_id);
        }
        return $applications;
    }

    public function getMyGroupsInfo(User $user)
    {
        return $isLeaderGroups = ExpertGroup::where('leader_expert_id', $user->id)->get();
    }

    public function getWaitingForReviewAwardInfoById($awardId)
    {
        $application = DB::table('applications as A')->where('A.id', $awardId)
            ->leftJoin('expert_scoring_application_relations as B', 'A.id', '=', 'B.application_id')
            ->orderBy('A.updated_at', 'desc')
            ->select('A.id', 'B.id as relation_id', 'A.user_id', 'A.project_name', 'A.award_id', 'A.status_id', 'B.expert_id', 'B.score', 'B.is_submitted', 'B.form_data', 'B.application_id')
            ->first();
        return $application;
    }

    public function getExpertGroupDetailsInfo(User $user, ExpertGroup $group)
    {
        $awardsIdsArr = AwardAndExpertGroupRelations::where('expert_group_id', $group->id)->pluck('award_id');
        $applications = DB::table('applications as A')->whereIn('A.id', $awardsIdsArr)
            ->leftJoin('expert_scoring_application_relations as B', 'A.id', '=', 'B.application_id')
            ->orderBy('A.updated_at', 'desc')
            ->select('A.id', 'B.id as relation_id', 'A.user_id', 'A.project_name', 'A.award_id', 'A.status_id', 'B.expert_id', 'B.score', 'B.is_submitted', 'B.form_data', 'B.application_id')
            ->get();
        $applicantIds = $applications->map(function ($record) {
            return $record->user_id;
        })->unique();
        $applicants = User::whereIn('id', $applicantIds)
            ->select('id', 'name', 'work_org_name', 'tel', 'email')
            ->get();
        $applicantMap = [];
        foreach ($applicants as $applicant) {
            $applicantMap[$applicant->id] = $applicant;
        }
        foreach ($applications as $application) {
            $applicant = $applicantMap[$application->user_id];
            $application->applicant_name = $applicant->name;
            $application->applicant_org_name = $applicant->work_org_name;
            $application->applicant_tel = $applicant->tel;
            $application->applicant_email = $applicant->email;
            $application->status_info = $this->getStatusDef($application->status_id);
        }
        return $applications;
    }

    /**
     * 根据投票结果计算应授奖项
     * @param $awardConfig \stdClass large_groups 表的 attrs 列的 award 属性
     * @param $plannedAwardLevel string 拟授奖项的名称
     * @param $totalVotes int 总票数
     * @param $agreeVotes int 同意票数
     * @return string 应授奖项的名称，空字符串代表无奖项
     */
    public function calcFinalAwardLevelByVote($awardConfig, $plannedAwardLevel, $totalVotes, $agreeVotes)
    {
        $awards = $awardConfig->awards;
        $plannedAwardIndex = -1;
        $min_support_rate = null;
        $on_low_support_rate = '';
        foreach ($awards as $i => $award) {
            if ($award->name === $plannedAwardLevel) {
                $plannedAwardIndex = $i;
                $min_support_rate = $award->min_support_rate;
                $on_low_support_rate = $award->on_low_support_rate;
                break;
            }
        }
        if ($agreeVotes * $min_support_rate[1] >= $min_support_rate[0] * $totalVotes) {
            return $plannedAwardLevel;
        } elseif ($on_low_support_rate === Consts::AWARD_POLICY_ON_LOW_SUPPORT_REJECT) {
            return Consts::AWARD_LEVEL_NONE;
        } else {  // AWARD_POLICY_ON_LOW_SUPPORT_DOWNGRADE, 降级
            return $awards[$plannedAwardIndex + 1]->name;
        }
    }
}
